﻿Imports System.Data.SqlClient
Public Class ItensCtasPagarDAL
#Region "Métodos"
    Function InserirItensCtasPagar(ByVal icpcodigo As Integer, ctpcodigo As Integer, ByVal datapgto As Nullable(Of Date), ByVal valparc As Decimal, ByVal pagam As Integer,
                             dtvencimento As Nullable(Of Date), ByVal vlrpago As Decimal, ByVal icpdesconto As Decimal, ByVal jam As Decimal, ByVal parcela As Integer,
                             ByVal conta As Integer, ByVal boletocheq As String, ByVal stacodigo As Integer) As Integer ', 
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            'cmd.CommandText = "insert into Financeiro.Ofi_ItensContasPagar(ctp_codigo,icp_datapagamento,icp_valor,pag_codigo,icp_datavencimento," & _
            '"icp_valorpago,icp_desconto,icp_jam, icp_parcela,con_codigo,icp_boletocheque,sta_codigo) values (@ctp_codigo,@icp_datapagamento,@icp_valor,@pag_codigo,@icp_datavencimento,@icp_valorpago,@icp_desconto," & _
            '"@icp_jam,@icp_parcela,@con_codigo,@icp_boletocheque,@sta_codigo)"

            cmd.CommandText = "execute SPItensContasPagar @ctp_codigo,@icp_codigo,@icp_datapagamento,@icp_valor,@pag_codigo,@icp_datavencimento,@icp_valorpago,@icp_desconto,@icp_jam,@icp_parcela,@con_codigo,@icp_boletocheque,@sta_codigo"
            cmd.Parameters.Add(New SqlParameter("@icp_codigo", SqlDbType.Int)).Value = icpcodigo
            cmd.Parameters.Add(New SqlParameter("@ctp_codigo", SqlDbType.Int)).Value = ctpcodigo
            If (datapgto.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@icp_datapagamento", SqlDbType.Date)).Value = datapgto
            Else
                cmd.Parameters.Add(New SqlParameter("@icp_datapagamento", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@icp_valor", SqlDbType.Decimal)).Value = valparc
            cmd.Parameters.Add(New SqlParameter("@pag_codigo", SqlDbType.Int)).Value = pagam
            If (dtvencimento.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@icp_datavencimento", SqlDbType.Date)).Value = dtvencimento   ' Format(dtvencimento, "MM-dd-yyyy"
            Else
                cmd.Parameters.Add(New SqlParameter("@icp_datavencimento", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@icp_valorpago", SqlDbType.Decimal)).Value = vlrpago
            cmd.Parameters.Add(New SqlParameter("@icp_desconto", SqlDbType.Decimal)).Value = icpdesconto
            cmd.Parameters.Add(New SqlParameter("@icp_jam", SqlDbType.Decimal)).Value = jam
            cmd.Parameters.Add(New SqlParameter("@icp_parcela", SqlDbType.Int)).Value = parcela
            cmd.Parameters.Add(New SqlParameter("@con_codigo", SqlDbType.Int)).Value = conta
            cmd.Parameters.Add(New SqlParameter("@icp_boletocheque", SqlDbType.VarChar)).Value = boletocheq
            cmd.Parameters.Add(New SqlParameter("@sta_codigo", SqlDbType.Int)).Value = stacodigo
            'cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usuario
            ' codigo = objconexao.executacmdAI(cmd, "Ofi_ItensContasPagar")
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw New Exception("Erro. ", ex)
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function


    Function updateItensCtasPagar(ByVal icpcodigo As Integer, ByVal ctpcodigo As Integer, ByVal datapgto As Nullable(Of Date), ByVal valparc As Decimal, ByVal pagam As Integer,
                             dtvencimento As Nullable(Of Date), ByVal vlrpago As Decimal, ByVal icpdesconto As Decimal, ByVal jam As Decimal, ByVal parcela As Integer,
                             ByVal conta As Integer, ByVal boletocheq As String, ByVal stacodigo As Integer) As Integer
        Dim cmd As SqlCommand
        Dim ret As Integer
        Try
            cmd = New SqlCommand
            cmd.CommandText = "UPDATE Financeiro.Ofi_ItensContasPagar SET ctp_codigo=@ctp_codigo,icp_datapagamento = @icp_datapagamento, icp_valor = @icp_valor, pag_codigo=@pag_codigo,icp_datavencimento=@icp_datavencimento, " & _
             "icp_valorpago = @icp_valorpago, icp_desconto = @icp_desconto,icp_jam = @icp_jam,icp_parcela = @icp_parcela,con_codigo=@con_codigo,icp_boletocheque=@icp_boletocheque," & _
             "sta_codigo=@sta_codigo WHERE  icp_codigo=@icp_codigo and ctp_codigo=@ctp_codigo"
            cmd.Parameters.Add(New SqlParameter("@icp_codigo", SqlDbType.Int)).Value = icpcodigo
            cmd.Parameters.Add(New SqlParameter("@ctp_codigo", SqlDbType.Int)).Value = ctpcodigo
            If (datapgto.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@icp_datapagamento", SqlDbType.Date)).Value = datapgto
            Else
                cmd.Parameters.Add(New SqlParameter("@icp_datapagamento", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@icp_valor", SqlDbType.Decimal)).Value = valparc
            cmd.Parameters.Add(New SqlParameter("@pag_codigo", SqlDbType.Int)).Value = pagam
            If (dtvencimento.HasValue) Then
                cmd.Parameters.Add(New SqlParameter("@icp_datavencimento", SqlDbType.Date)).Value = dtvencimento
            Else
                cmd.Parameters.Add(New SqlParameter("@icp_datavencimento", SqlDbType.Date)).Value = DBNull.Value
            End If
            cmd.Parameters.Add(New SqlParameter("@icp_valorpago", SqlDbType.Decimal)).Value = vlrpago
            cmd.Parameters.Add(New SqlParameter("@icp_desconto", SqlDbType.Decimal)).Value = icpdesconto
            cmd.Parameters.Add(New SqlParameter("@icp_jam", SqlDbType.Decimal)).Value = jam
            cmd.Parameters.Add(New SqlParameter("@icp_parcela", SqlDbType.Int)).Value = parcela
            cmd.Parameters.Add(New SqlParameter("@con_codigo", SqlDbType.Int)).Value = conta
            cmd.Parameters.Add(New SqlParameter("@icp_boletocheque", SqlDbType.VarChar)).Value = boletocheq
            cmd.Parameters.Add(New SqlParameter("@sta_codigo", SqlDbType.Int)).Value = stacodigo
            ret = objconexao.executaCmd(cmd)

        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
    Function excluir(ByVal codigo As Integer) As Boolean
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "execute SPExcluiItensContasPagar @codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            objconexao.executaCmd(cmd)
            Return True
        Catch ex As Exception
            Throw ex
            Return False
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
#Region "Buscar e localizar" 'tabela itens contas a pagar
    Function buscar(ByVal descricao As String, tipo As Integer) As DataTable
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0  'esta sendo usado em relatorios por icp_codigo
                    cmd.CommandText = "select * from Financeiro.Ofi_ItensContasPagar where icp_codigo= @codigo "
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case 1
                    'por codigo Financeiro.Ofi_Itens
                    cmd.CommandText = "select * from Financeiro.Ofi_ItensContasPagar where icp_codigo= @codigo and sta_codigo = 4"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case 2 '
                    cmd.CommandText = "select icp_codigo,icp_parcela,icp_valor,icp_datavencimento,icp_datapagamento,sta_descricao from vw_FinContasPagar where ctp_codigo= @codigo and sta_codigo =4 order by icp_datavencimento asc"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
                Case Else
                    'todos
                    cmd.CommandText = "select * from Financeiro.Ofi_ItensContasPagar where ctp_codigo= @codigo" 'este grid das parcelas
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(descricao)
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
    'Function localiza_itens(ByVal cod As Integer, ByVal tip As Integer) As DataTable  'Modificar
    '    Dim tab As DataTable
    '    Dim cmd As SqlCommand
    '    Try
    '        cmd = New SqlCommand
    '        Select Case tip
    '            Case 1
    '                cmd.CommandText = "select * from Financeiro.Ofi_ItensContasPagar where ctp_codigo=@codigo " 'tem q pegar pelo ctp codigo Ctas pagar
    '                cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
    '            Case Else
    '                cmd.CommandText = "select * from Financeiro.Ofi_ItensContasPagar where icp_codigo=@codigo " 'tem q pegar pelo itp codigo itens Ctas
    '                cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
    '        End Select
    '        tab = objconexao.retdadosTab(cmd)
    '    Catch ex As Exception
    '        Throw New Exception("", ex)
    '    Finally
    '        cmd = Nothing
    '    End Try
    '    Return tab
    'End Function
    Function localizapgtos(ByVal codigo As Integer) As DataTable  'localiza dos pagamentos
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Ofi_ItensContasPagar where icp_codigo=@codigo " 'tem q pegar pelo ctp codigo
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function
#End Region
#Region "Soma Parcelas"
    Function Somaparcelas(ByVal codigo As Integer) As Decimal 'Vendas
        Dim cmd As SqlCommand
        Dim ret As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select SUM(icp_valor) soma from Financeiro.Ofi_ItensContasPagar where ctp_codigo=@codigo and sta_codigo=4"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        If (ret.Rows.Count > 0 And ret(0)("soma").ToString.Trim <> "") Then
            Return Decimal.Parse(ret(0)("soma").ToString)
        Else : Return 0
        End If
    End Function
    Function Somaparcelatotal(ByVal codigo As Integer) As Decimal 'Vendas
        Dim cmd As SqlCommand
        Dim ret As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select SUM(icp_valor) soma from Financeiro.Ofi_ItensContasPagar where ctp_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaConsulta(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        If (ret.Rows.Count > 0 And ret(0)("soma").ToString.Trim <> "") Then
            Return Decimal.Parse(ret(0)("soma").ToString)
        Else : Return 0
        End If
    End Function
#End Region
End Class
